from db.mysql_db import pool


class NewsDao:
    # 查询待审批新闻列表
    def pending_news_list(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select n.id, n.title, t.type, u.username " \
                  "from t_news n join t_type t on n.type_id = t.id " \
                  "join t_user u on n.editor_id = u.id " \
                  "where n.state=%s " \
                  "order by create_time " \
                  "desc limit %s, %s"
            cursor.execute(sql, ("待审批", (page - 1) * 10, 10))

            news_list = cursor.fetchall()

            return news_list

        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询待审批新闻的总页数
    def pending_news_count_page(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select ceil(count(*)/10) from t_news where state=%s"
            cursor.execute(sql, ["待审批"])

            count_page = cursor.fetchone()[0]

            return count_page
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 审批新闻
    def approval_news(self, news_id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()

            sql = "update t_news set state=%s where id=%s"
            cursor.execute(sql, ("已审批", news_id))

            con.commit()

        except Exception as e:
            if "con" in dir():
                con.rollback()
                print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询新闻列表
    def query_news_list(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select n.id, n.title, t.type, u.username " \
                  "from t_news n join t_type t on n.type_id = t.id " \
                  "join t_user u on n.editor_id = u.id " \
                  "order by create_time " \
                  "desc limit %s, %s"
            cursor.execute(sql, ((page - 1) * 10, 10))

            news_list = cursor.fetchall()

            return news_list
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询新闻总页数
    def query_total_number_news(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()

            sql = "select ceil(count(*)/10) from t_news"
            cursor.execute(sql)

            count_page = cursor.fetchone()[0]

            return count_page
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 删除新闻
    def delete_new_byid(self, news_id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()

            sql = "delete from t_news where id=%s"
            cursor.execute(sql, [news_id])

            con.commit()

        except Exception as e:
            if "con" in dir():
                con.rollback()
                print(e)
        finally:
            if "con" in dir():
                con.close()